﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WebAuto
{
    public partial class DBTestRecorder : Form
    {
        private int lastCheck = -1;


        public DBTestRecorder()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void _getDatabases()
        {
            ddlDatabases.Items.Clear();
            try
            {
                string connString =_getServerConnectionString();

                using (SqlConnection sqlConn = new SqlConnection(connString))
                {
                    sqlConn.Open();
                    DataTable tblDatabases = sqlConn.GetSchema("Databases");
                    sqlConn.Close();
                    foreach (DataRow dr in tblDatabases.Rows)
                    {
                        ddlDatabases.Items.Add(dr[0].ToString());

                    }
                }
            }catch(Exception ex)
            {
                ddlDatabases.Items.Add("Error");
                MessageBox.Show("Error while connecting to the Sql Server." + ex.Message);

            }

        }

        private void txtSqlServer_Leave(object sender, EventArgs e)
        {

            


        }

        private void ddlDatabases_SelectedIndexChanged(object sender, EventArgs e)
        {
            if(ddlDatabases.Items.Count>1)
            {
                rdTables.Enabled = true;
                rdSP.Enabled = true;
                rdFunc.Enabled = true;

            }else
            {
                rdTables.Enabled = false;
                rdSP.Enabled = false;
                rdFunc.Enabled = false;

            }
        }



        private void rdTables_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                checkedListBox1.Items.Clear();
                String connString = _getDBConnectionString();

                using (SqlConnection sqlConn = new SqlConnection(connString))
                {
                    sqlConn.Open();
                    System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
                    SqlCom.Connection = sqlConn;
                    SqlCom.CommandType = CommandType.Text;
                    SqlCom.CommandText = "SELECT name FROM sysobjects WHERE xtype = 'U' order by name";

                    System.Data.SqlClient.SqlDataReader SqlDR;
                    SqlDR = SqlCom.ExecuteReader();

                    while(SqlDR.Read())
                    {
                        checkedListBox1.Items.Add(SqlDR.GetString(0));
                        
                    }
                    
                }
            }
            catch (Exception ex)
            {
                ddlDatabases.Items.Add("Error");
                MessageBox.Show("Error while getting the table list." + ex.Message);

            }
        }

        private void rdSP_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                checkedListBox1.Items.Clear();
                String connString = _getDBConnectionString();

                using (SqlConnection sqlConn = new SqlConnection(connString))
                {
                    sqlConn.Open();
                    System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
                    SqlCom.Connection = sqlConn;
                    SqlCom.CommandType = CommandType.Text;
                    SqlCom.CommandText = "SELECT name FROM sysobjects WHERE xtype = 'p' order by name";

                    System.Data.SqlClient.SqlDataReader SqlDR;
                    SqlDR = SqlCom.ExecuteReader();

                    while (SqlDR.Read())
                    {
                        checkedListBox1.Items.Add(SqlDR.GetString(0));

                    }

                }
            }
            catch (Exception ex)
            {
                ddlDatabases.Items.Add("Error");
                MessageBox.Show("Error while getting the Stored procedures list." + ex.Message);

            }
        }

        private void rdFunc_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                checkedListBox1.Items.Clear();
                String connString = _getDBConnectionString();

                using (SqlConnection sqlConn = new SqlConnection(connString))
                {
                    sqlConn.Open();
                    System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
                    SqlCom.Connection = sqlConn;
                    SqlCom.CommandType = CommandType.Text;
                    SqlCom.CommandText = "SELECT name FROM sysobjects WHERE xtype  IN ('FN', 'IF', 'TF')  order by name";

                    System.Data.SqlClient.SqlDataReader SqlDR;
                    SqlDR = SqlCom.ExecuteReader();

                    while (SqlDR.Read())
                    {
                        checkedListBox1.Items.Add(SqlDR.GetString(0));

                    }

                }
            }
            catch (Exception ex)
            {
                ddlDatabases.Items.Add("Error");
                MessageBox.Show("Error while getting the Functions list." + ex.Message);

            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                if (!_isConnPassed())
                {

                    return;
                }


                Dictionary<string, string> listOfParameters = new Dictionary<string, string>();
                String connString = _getDBConnectionString();
                string objectName = checkedListBox1.CheckedItems[0].ToString();
                using (SqlConnection sqlConn = new SqlConnection(connString))
                {
                    sqlConn.Open();
                    System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
                    SqlCom.Connection = sqlConn;
                    SqlCom.CommandType = CommandType.Text;
                    SqlCom.CommandText = "select * from information_schema.parameters where specific_name='" + objectName + "'";

                    System.Data.SqlClient.SqlDataReader SqlDR;
                    SqlDR = SqlCom.ExecuteReader();
                   

                    while (SqlDR.Read())
                    {
                        string paraName = SqlDR["PARAMETER_NAME"].ToString();
                        string paraType = SqlDR["DATA_TYPE"].ToString();
                        string paraLength = SqlDR["CHARACTER_MAXIMUM_LENGTH"].ToString();
                        string schema = SqlDR["SPECIFIC_SCHEMA"].ToString();
                        if(!listOfParameters.ContainsKey(paraName))
                        {

                            listOfParameters.Add(paraName, paraType + "," + paraLength + "," + schema);
                        }
                    }

                }

                if(listOfParameters.Count>0)
                {
                    string testDetails = @"Browser?Data|IE,TestDB_" + objectName + ",TestDB_" + objectName + ".txt,some description,about:blank," + txtTestResultsLocation.Text + "\\" + objectName + "," + txtTestRepoLocation.Text + "," + connString + "; Integrated Security=True,about:blank,1,,>\n";
                    string schema = "";
                    string paramList = "~";
                    string varaiableListTest = "~";
                    string paramListTest = "~";
                    foreach(var item in listOfParameters)
                    {
                        string paraName = item.Key;
                        string paraType = item.Value.Split(',')[0];
                        string paraLength = item.Value.Split(',')[1];
                        schema = item.Value.Split(',')[2];
                        if (paraType.ToLower() == "varchar" || paraType.ToLower() == "nvarchar" || paraType.ToLower() == "char" || paraType.ToLower() == "text"||
                            paraType.ToLower() == "datetime"||
                            paraType.ToLower() == "smalldatetime"
                            )
                        {
                            paramList += paraName + "='#" + paraName.Replace("@", "") + "', ";
                            
                        }else
                        {
                            paramList += paraName + "=#" + paraName.Replace("@", "") + ", ";
                           
                        }

                        int maxLen = 10;
                        try
                        {
                            maxLen = Convert.ToInt32(paraLength);
                        }catch{}
                        if(maxLen>10)
                        {
                            maxLen = 10;
                        }
                        string varValue = "";
                        switch(paraType.ToLower())
                        {
                            case "varchar":
                            case "nvarchar":
                            case "char":
                            case "text":
                                varValue = _randomString(maxLen);
                                break;
                            case "int":
                            case "float":
                            case "bigint":
                                varValue = _randomNumber(maxLen);
                                break;
                            case "tinyint":
                                varValue = _randomNumber(2);
                                break;
                            case "datetime":
                                varValue = DateTime.Now.ToString("dd MMM yyyy HH:mm:ss");
                                break;
                            case "smalldatetime":
                                varValue = DateTime.Now.ToString("dd MMM yyyy");
                                break;

                        }

                        varaiableListTest += "Variable?Set|data" + paraName.Replace("@","") + ":" + varValue + ">"+Environment.NewLine;
                        paramListTest += "[#" + paraName.Replace("@", "") + "=Variable[data" + paraName.Replace("@", "") + "]],";

                    }
                    if (!string.IsNullOrEmpty(paramList))
                    {
                        paramList = paramList.Replace("~", "") + "~";
                        paramList = paramList.Replace(", ~", "");
                    }

                    if (!string.IsNullOrEmpty(varaiableListTest))
                    {
                        varaiableListTest = varaiableListTest.Replace("~", "") + "~";
                        varaiableListTest = varaiableListTest.Replace(",~", "");
                        varaiableListTest = varaiableListTest.Replace(Environment.NewLine+"~", "");
                    }

                    if (!string.IsNullOrEmpty(paramListTest))
                    {
                        paramListTest = paramListTest.Replace("~", "") + "~";
                        paramListTest = paramListTest.Replace(",~", "");
                    }

                    string sqlFileName = @"\Test_" + objectName + ".sql";
                    string sqlFullFileName = txtTestRepoLocation.Text + sqlFileName;
                    if(File.Exists(sqlFullFileName))
                    {
                        File.Delete(sqlFullFileName);
                    }

                    using (StreamWriter sw = File.CreateText(sqlFullFileName))
                    {
                        sw.WriteLine("USE " + ddlDatabases.Text + "\n");
                        sw.WriteLine("GO\n");
                        sw.WriteLine("EXEC " + schema +"."+objectName + " " + paramList);
                    }

                    string testFileName = @"\TestDB_" + objectName+".txt";
                    string testFullFileName = txtTestFileLocation.Text + testFileName;
                    if (File.Exists(testFullFileName))
                    {
                        File.Delete(testFullFileName);
                    }

                    using (StreamWriter sw = File.CreateText(testFullFileName))
                    {
                        sw.WriteLine(testDetails+"\n");
                        sw.WriteLine(varaiableListTest);
                        sw.WriteLine("Data?Exec|" + sqlFileName.Replace(@"\", "") + ":" + paramListTest + ":ds2>\n");
                       // sw.WriteLine("//Data retrievel is DataSet name, column index followed by row index\n");
                        sw.WriteLine("Assert?AreEqual|Please enter a value to assert:Data[ds2,0,0]>\n");
                        sw.WriteLine("EndTest?End|true");
                    }
                    MessageBox.Show("Test created successfully.The test will initialy fail.Please edit the test to Assert for correct values.");

                   
                    ProcessStartInfo startInfo = new ProcessStartInfo("notepad.exe");
                    startInfo.Arguments = testFullFileName;
                    Process.Start(startInfo);

                }else
                {
                    MessageBox.Show(
                        "DB Test cannot be created because the object you selected does not have any parameters!");
                }

            }
            catch (Exception ex)
            {
                ddlDatabases.Items.Add("Error");
                MessageBox.Show("Error while getting the Functions list." + ex.Message);

            }
        }

        private void checkedListBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            int toUncheck = lastCheck;
            if (toUncheck != -1)
                checkedListBox1.SetItemChecked(toUncheck, false);
            lastCheck = checkedListBox1.SelectedIndex;
            checkedListBox1.SetItemChecked(lastCheck, true);

        }


        private string _randomString(int maxLength)
        {
            string strPwdchar = "abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            string strPwd = "";
            Random rnd = new Random();
            for (int i = 0; i <= maxLength; i++)
            {
                int iRandom = rnd.Next(0, strPwdchar.Length - 1);
                strPwd += strPwdchar.Substring(iRandom, 1);
            }
            return strPwd;
        }

        private string _randomNumber(int max)
        {
            Random random = new Random();
            return random.Next(0, max).ToString();
        }

        private void ddlSqlLoginType_SelectedIndexChanged(object sender, EventArgs e)
        {
            if(ddlSqlLoginType.SelectedIndex==1)
            {
                lblPassword.Enabled = true;
                lblUserName.Enabled = true;
                txtSqlServerPassword.Enabled = true;
                txtSqlUserName.Enabled = true;
            }else
            {
                lblPassword.Enabled = false;
                lblUserName.Enabled = false;
                txtSqlServerPassword.Enabled = false;
                txtSqlUserName.Enabled = false;
                txtSqlServerPassword.Text = "";
                txtSqlUserName.Text = "";
            }
        }


        private string _getServerConnectionString()
        {
            string returnValue = "";
            if (ddlSqlLoginType.SelectedIndex == 1)
            {
                returnValue = @"Server=" + txtSqlServer.Text + ";User Id=" + txtSqlUserName.Text + ";Password=" + txtSqlServerPassword.Text + ";";
                
            }else
            {
                returnValue = @"Server=" + txtSqlServer.Text + ";Integrated Security=True;";
            }
            return returnValue;
        }

        private string _getDBConnectionString()
        {
            string returnValue = "";
            if (ddlSqlLoginType.SelectedIndex == 1)
            {
                returnValue = @"Server=" + txtSqlServer.Text + ";Database=" + ddlDatabases.Text + ";User Id=" + txtSqlUserName.Text + ";Password=" + txtSqlServerPassword.Text + ";";

            }
            else
            {
                returnValue = @"Server=" + txtSqlServer.Text + ";Database=" + ddlDatabases.Text + ";Integrated Security=True;";
            }
            return returnValue;
        }


        private bool _isConnPassed()
        {

            if (ddlSqlLoginType.SelectedIndex == 1)
            {
                if (txtSqlServerPassword.Text == "" || txtSqlUserName.Text == "")
                {
                    MessageBox.Show("Please enter a username and password!");
                    return false;
                }

            }

            string connString = _getServerConnectionString();
            bool _isPassed = false;
            try
            {
                using (SqlConnection sqlConn = new SqlConnection(connString))
                {
                    sqlConn.Open();
                    _isPassed = true;
                }

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Test Connection failed!" + ex.Message);

            }

            return false;

        }

        private void button3_Click(object sender, EventArgs e)
        {
           
            if (_isConnPassed())
            {
                MessageBox.Show("Test Connection was successfull");
                _getDatabases();
            }
        }

       
    }
}

